<?php

class PayoutsController extends Controller
{
	var $year;
	var $week_no;
	var $bv_max = 5000;
	var $bv_side_max = 2500;
	var $bv_weight = 100;
	var $bv_divisor = 25; 
	var $payout_limit = 50000;
	var $bv_excess_multiplier = 1;
	var $bv_excess_minimum = 12;
	
	/**
	 * @var string the default layout for the views. Defaults to '//layouts/column2', meaning
	 * using two-column layout. See 'protected/views/layouts/column2.php'.
	 */
	//public $layout='//layouts/column2';

	/**
	 * @return array action filters
	 */
	public function filters()
	{
		return array(
			'accessControl', // perform access control for CRUD operations
		);
	}

	/**
	 * Specifies the access control rules.
	 * This method is used by the 'accessControl' filter.
	 * @return array access control rules
	 */
	public function accessRules()
	{
		return array(
			array('allow',  // allow all users to perform 'index' and 'view' actions
				'actions'=>array('index','view'),
				'users'=>array('*'),
			),
			array('allow', // allow authenticated user to perform 'create' and 'update' actions
				'actions'=>array('create','update'),
				'users'=>array('@'),
			),
			/*array('allow', // allow admin user to perform 'admin' and 'delete' actions
				'actions'=>array('admin','delete'),
				'users'=>array('admin'),
			),*/
			array('allow', // allow admin user to perform 'admin' and 'delete' actions
				'expression'=>'$user->isAdmin',
			),
			array('deny',  // deny all users
				'users'=>array('*'),
			),
		);
	}

	/**
	 * Displays a particular model.
	 * @param integer $id the ID of the model to be displayed
	 */
	public function actionView($id)
	{
		$this->render('view',array(
			'model'=>$this->loadModel($id),
		));
	}

	/**
	 * Creates a new model.
	 * If creation is successful, the browser will be redirected to the 'view' page.
	 */
	public function actionCreate()
	{
		$model=new Payouts;

		// Uncomment the following line if AJAX validation is needed
		// $this->performAjaxValidation($model);

		if(isset($_POST['Payouts']))
		{
			$model->attributes=$_POST['Payouts'];
			if($model->save())
				$this->redirect(array('view','id'=>$model->payout_id));
		}

		$this->render('create',array(
			'model'=>$model,
		));
	}

	/**
	 * Updates a particular model.
	 * If update is successful, the browser will be redirected to the 'view' page.
	 * @param integer $id the ID of the model to be updated
	 */
	public function actionUpdate($id)
	{
		$model=$this->loadModel($id);

		// Uncomment the following line if AJAX validation is needed
		// $this->performAjaxValidation($model);

		if(isset($_POST['Payouts']))
		{
			$model->attributes=$_POST['Payouts'];
			if($model->save())
				$this->redirect(array('view','id'=>$model->payout_id));
		}

		$this->render('update',array(
			'model'=>$model,
		));
	}

	/**
	 * Deletes a particular model.
	 * If deletion is successful, the browser will be redirected to the 'admin' page.
	 * @param integer $id the ID of the model to be deleted
	 */
	public function actionDelete($id)
	{
		if(Yii::app()->request->isPostRequest)
		{
			// we only allow deletion via POST request
			$this->loadModel($id)->delete();

			// if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser
			if(!isset($_GET['ajax']))
				$this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('admin'));
		}
		else
			throw new CHttpException(400,'Invalid request. Please do not repeat this request again.');
	}

	/**
	 * Lists all models.
	 */
	public function actionIndex()
	{
		$dataProvider=new CActiveDataProvider('Payouts');
		$this->render('index',array(
			'dataProvider'=>$dataProvider,
		));
	}
	
	/**
	 * Lists all models.
	 */

	/**
	 * Manages all models.
	 */
	public function actionAdmin()
	{
		$model=new Payouts('search');
		//$model->with('Members')->findAll();
		$model->unsetAttributes();  // clear any default values
		if(isset($_GET['Payouts']))
			$model->attributes=$_GET['Payouts'];
  
        $this->logAction();
		
		$this->year = date("Y");
	    $this->week_no = date("W");
		
		if ( isset($_POST['week_no']))
		{
			$this->year = $_POST['year'];
			$this->week_no = $_POST['week_no'];
			#step 1
			$this->getPersonalBV();			
			#step 2
			$this->getLeftRightBV();
			#step 3
			$this->getMatchBV();
		} 

		$this->render('admin',array(
			'model'=>$model,
		));
	}

	/**
	 * Returns the data model based on the primary key given in the GET variable.
	 * If the data model is not found, an HTTP exception will be raised.
	 * @param integer the ID of the model to be loaded
	 */
	public function loadModel($id)
	{
		$model=Payouts::model()->findByPk($id);
		if($model===null)
			throw new CHttpException(404,'The requested page does not exist.');
		return $model;
	}

	/**
	 * Performs the AJAX validation.
	 * @param CModel the model to be validated
	 */
	protected function performAjaxValidation($model)
	{
		if(isset($_POST['ajax']) && $_POST['ajax']==='payouts-form')
		{
			echo CActiveForm::validate($model);
			Yii::app()->end();
		}
	}
	
	
	#### COMPUTATION FUNCTIONS
	public function getPersonalBV () 
	{
		$connection = Yii::app()->db;

		$year = $this->year;
		$week_no = $this->week_no;
		
		$sql = "SELECT a.member_id, SUM(a.quantity*b.bv_points) as points
		               FROM gn_sales AS a 
		          LEFT JOIN gn_products AS b 
		                 ON (a.product_id=b.product_id)
		              WHERE (WEEK(a.dt_sale)=$week_no AND YEAR(a.dt_sale)=$year) 
		           GROUP BY a.member_id";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data = $dataReader->read() ) 
	    {
	    	//print_r($data);
			
	    	$sql= "";
	        $payout_id = $this->get_gnPayoutsID($year, $week_no, $data['member_id']);
	    	
	    	if ( $payout_id > 0 ) 
			{
			   #update
			   $bv_personal = $data['points'];
			   if ( $bv_personal < 1 )
			     $bv_personal=0;
			   
			   $sql = "UPDATE gn_payouts 
			              SET bv_personal=$bv_personal
			            WHERE payout_id=$payout_id";	
			}
			else 
			{
			   #insert	
			   $member_id = $data['member_id'];
			   $bv_personal = $data['points'];
			   $sql = "INSERT INTO gn_payouts 
			                   SET member_id=$member_id,
			                       bv_personal=$bv_personal,
			                       year=$year,
			                       week_no=$week_no,
			                       status='pending'";
			}
			
			$connection->createCommand($sql)->query();
	    }	  
	}
	
	public function getLeftRightBV () {
		$connection = Yii::app()->db;
		
		$year = $this->year;
		$week_no = $this->week_no;
		
		#get only active members status=1
		$sql = "SELECT a.member_id  
		               FROM gn_members AS a
		    LEFT OUTER JOIN gn_payouts AS b
		                 ON (a.member_id=b.member_id)
		              WHERE b.week_no=$week_no AND b.year=$year
		                AND a.status_id=1";
				   
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data = $dataReader->read() ) 
	    {
	       //print_r($data);
	       $member_id = $data['member_id']; 
	       $child = $this->getChildren($member_id, $year, $week_no);
		   
		   //print_r ($child);
		   
	       $bv_left = 0;
	       $bv_right = 0; 
	       
	       if ( isset($child['left']) )
		   {
		   	 $bv_left = $child['left']['bv_personal'];
	         $this->getDownlineBV($child['left']['member_id'], 1, $bv_left );
	       } 
	         
		   
		   if ( isset($child['right']) )
	       {
	       	  $bv_right = $child['right']['bv_personal'];
	       	  $this->getDownlineBV($child['right']['member_id'], 1, $bv_right );
	       }  
	         
	       $payout_id = $this->get_gnPayoutsID($year, $week_no, $data['member_id']);
	       
	       #remove carryover by sir ador July 2, 2012
	       //$carryover = $this->getPrevWeekCarryOver($member_id);
	       
	       //$bv_left += $carryover['bv_left_carryover'];
	       //$bv_right += $carryover['bv_right_carryover'];
	       
	       $sql = "UPDATE gn_payouts 
	                  SET bv_left=$bv_left, bv_right=$bv_right 
	                WHERE payout_id=$payout_id";
		   
		   $connection->createCommand($sql)->query();
		}
	}

    public function getMatchBV()
	{
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no;
		
		#get only active members status=1
		$sql = "SELECT payout_id, bv_left, bv_right, bv_personal  
		               FROM gn_payouts 
		              WHERE week_no=$week_no AND year=$year";
				   
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data=$dataReader->read() ) {
			$payout_id = $data['payout_id'];	
			$bv_matched = $this->computeMatchedBV($data['bv_left'], $data['bv_right']);
			
			if ( $bv_matched > $this->bv_max )
			  $bv_matched = $this->bv_max;
			
			$bv_side_matched = $bv_matched/2;
			
			$bv_left_excess = $this->computeCarryOver( $data['bv_left'], $bv_side_matched  );
			$bv_right_excess = $this->computeCarryOver( $data['bv_right'], $bv_side_matched );
			
			if ( $data['bv_personal'] >= $this->bv_excess_minimum )
			{
			  $total_excess = $bv_left_excess  + $bv_right_excess; 
			  $excess_payout = $total_excess * $this->bv_excess_multiplier;
			  $payout = $this->getPayout($bv_matched);
			  $total_payout = $payout + $excess_payout;
			}
			
			
			$sql = "UPDATE gn_payouts 
			           SET bv_match=$bv_matched,  
			               payout=$total_payout,
			               bv_left_carryover=$bv_left_excess,
			               bv_right_carryover=$bv_right_excess       
			        WHERE payout_id=$payout_id";
			
			$connection->createCommand($sql)->query();
	    }
   }

   public function computeCarryOver( $side_points, $bv_limit )
   {
   	  #check if less than minumum, dont compute
   	  if ( $bv_limit < 25)
	    return $side_points;
	  
   	  $carryover = 0;  
	  
	  if ( $side_points >= $bv_limit )
	    $carryover = $side_points - $bv_limit;
	        
	  return $carryover;
   }
   
	
   public function computeMatchedBV($bv_left, $bv_right) 
   {
		$raw_match = 0;
		
		if ( ($bv_left==0) || ($bv_right==0) )
		  $raw_match = 0; #no match if one side is empty
		else if ( $bv_left > $bv_right )
		  $raw_match = $bv_left - $bv_right;
		else if ( $bv_right > $bv_left )
		  $raw_match = $bv_right - $bv_left;
		else 
		  $raw_match = $bv_left; #left and right are equal
		  
		$bv_matched = $raw_match * 2;
		
		return $bv_matched;
		
		/*
		#5000 is maximum limit per week, 2500 per side
		if ( $bv_matched > 5000 ) {
			return 5000;
		} else {
			return $bv_matched;
		}
		*/ 	
	}
	
	public function getPayout( $bv_matched )
	{
		#dont compute if less than minimum 25
		if ( $bv_matched < 25 )
		    return 0;
		
		if ( $bv_matched > $this->bv_max )
		   $bv_matched = $this->bv_max;
		
		$bv_side_match = $bv_matched/2;
		
		$bv_multiplier = $this->getBVMultiplier($bv_matched);
		$payout = ($bv_side_match/$this->bv_divisor) * $bv_multiplier;
		//$payout = $payout * 1.12; #get Tax
		
		//if ( $payout > $this->payout_limit )
		  //return $this->payout_limit;
		//else {
		  return $payout;	
		//}
	}
	
	public function getBVMultiplier($bv_matched)
	{
		$bv_side = $bv_matched/2;
		
		#set level bracket array
		$data[0]['bv_match']       = 25;
		$data[0]['bv_multiplier']  = 100;
		$data[1]['bv_match']       = 100;
		$data[1]['bv_multiplier']  = 150;
		$data[2]['bv_match']       = 300;
		$data[2]['bv_multiplier']  = 200;
		$data[3]['bv_match']       = 700;
		$data[3]['bv_multiplier']  = 300;
		$data[4]['bv_match']       = 1500;
		$data[4]['bv_multiplier']  = 400;
		$data[5]['bv_match']       = 2500;
		$data[5]['bv_multiplier']  = 500;
		
		#else is max
		$data[6]['bv_match']       = 999999999;
		$data[6]['bv_multiplier']  = 500;
		
		#loop while bv_multiplier is matched
		$i = 0;	
		while ( isset($data[$i]) ) {
			
			$n=$i+1;
			if ( ($bv_side>=$data[$i]['bv_match']) && ($bv_side<$data[$n]['bv_match']) ) {
			   return $data[$i]['bv_multiplier'];
			}
			
			$i++;
		}
		
		return 500; #means maximum is reached
	}
	
	public function getBVDivisor($bv_matched)
	{
			
		$bv_half = $bv_matched/2;
		
		#set level bracket array
		$data[0]['bv_match']   = 25;
		$data[0]['bv_divisor'] = 100;
		$data[1]['bv_match']   = 100;
		$data[1]['bv_divisor'] = 150;
		$data[2]['bv_match']   = 300;
		$data[2]['bv_divisor'] = 200;
		$data[3]['bv_match']   = 700;
		$data[3]['bv_divisor'] = 300;
		$data[4]['bv_match']   = 1500;
		$data[4]['bv_divisor'] = 400;
		$data[5]['bv_match']   = 2500;
		$data[5]['bv_divisor'] = 500;
		
		#loop while bv_multiplier is matched
		$i = 0;	
		while ( $data[$i] ) {
			
			if ( $data[$i]['bv_match'] >= $bv_half ) {
			   return $data[$i]['bv_divisor']*2;
			}
			
			$i++;
		}
		
		return 500; #means maximum is reached
	}
	
	public function getChildren($member_id=0)
	{
		$connection = Yii::app()->db;
        $year = $this->year;
		$week_no = $this->week_no;
		/*
		$week_no = $_POST['week_no'];
		$year = $_POST['year'];
		$sql = "SELECT a.member_id, a.position, b.bv_personal
		               FROM gn_members AS a 
		          LEFT OUTER JOIN gn_payouts AS b
		               ON (a.member_id=b.member_id)
		               WHERE a.sponsor_id=$member_id
		                AND ((b.year=$year AND b.week_no=$week_no) OR (ISNULL(b.year) AND ISNULL(b.week_no)))
		         ";*/
				 
		
		$sql = "SELECT member_id, position, status_id
		               FROM gn_members 
		               WHERE sponsor_id=$member_id";
		               
		//echo "<hr>$sql<br>";			  
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
		
		$child = array();
		while ( $data = $dataReader->read() ) 
	    {
	       if ( $data['status_id']==1)	
	          $data['bv_personal'] = $this->getMemberPersonalBV($data['member_id']);
		   else 
			  $data['bv_personal'] = 0;    
		   
	       $child[$data['position']] = $data;
		}
		
		//print_r ($child);
		return $child;
	}
	
	public function getMemberPersonalBV($member_id=0)
	{
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no;
		
		$sql = "SELECT bv_personal 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		           AND week_no=$week_no
		           AND year=$year";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_personal']) )
	       return $data['bv_personal'];
        else
           return 0;
	}
	
	
	public function getPrevWeekCarryOver($member_id=0)
	{
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no-1;
		
		#check if end of prev year
		if ( $week_no == 0 )
		{
		   $week_no = 52;
		   $year = $year - 1;
		}  
		
		
		$sql = "SELECT bv_left_carryover, bv_right_carryover 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		           AND week_no=$week_no
		           AND year=$year";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    return $data;
	}
	
	
	
	public function get_gnPayoutsID( $year, $week_no, $member_id )
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT payout_id  
		               FROM gn_payouts
		              WHERE week_no=$week_no 
		                AND year=$year
                        AND member_id=$member_id		           
		";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
		$data = $dataReader->read();
		
		if ( isset($data['payout_id']) )
		  return $data['payout_id'];
        else
          return 0;
	}
	
	public function getDownlineBV($member_id, $level, &$total_points) 
	{
		if ($level==10) return;
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no;
		
		/*
		$sql = "SELECT a.member_id, b.bv_personal
		                      FROM gn_members AS a
		           LEFT OUTER JOIN gn_payouts AS b
		                        ON (a.member_id=b.member_id)
		                     WHERE a.sponsor_id=$member_id
		                       AND ((b.year=$year AND b.week_no=$week_no) OR (ISNULL(b.year) AND ISNULL(b.week_no)))
		                       
		";
		 * */
		
		$sql = "SELECT member_id, status_id  
		              FROM gn_members 
		                     WHERE sponsor_id=$member_id		                       
		";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
		
		while ( $data = $dataReader->read() ) 
		{
			if ( $data['status_id']==1)	
	          $data['bv_personal'] = $this->getMemberPersonalBV($data['member_id']);
		   else 
			  $data['bv_personal'] = 0;    
		   
			$total_points = $total_points + $data['bv_personal'];
			$this->getDownlineBV($data['member_id'], $level++, $total_points );
		}
	}
   
    public function array_implode( $glue, $separator, $array ) {
		if ( ! is_array( $array ) ) return $array;
		$string = array();
		foreach ( $array as $key => $val ) {
			if ( is_array( $val ) )
				$val = implode( ',', $val );
			$string[] = "{$key}{$glue}{$val}";	   
		}
		
		return implode( $separator, $string );
	}
	
    public function logAction()
	{
		$datetime = date("Y-m-d H:i:s");
		$user = Yii::app()->user->id;
		$action =  $this->id . '/' . $this->action->id;
		$data   = $this->array_implode( "=", "::", $_GET );
		$data  .= $this->array_implode( "=", "::", $_POST );
		
		$sql = "INSERT INTO gn_audit_trail SET
		               datetime='$datetime', 
					   user='$user',
					   action='$action',
					   data='$data'";
					   
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
	}
}
